<?php
/**
 * Student Transfer Controller
 *
 * Student Transfer Management
 * student entry
 *
 * @version $Id: TransferController.php 85 2010-04-25 13:03:41Z yingkuang.chen@gmail.com $
 * @license New BSD License
 * @uses Zend_Controller_Action
 * @package student
 * @subpackage Controller
 */
class Student_TransferController extends Zend_Controller_Action
{
    private $_arrayData;
    private $_teacherSn;
    public function init()
    {
        /* Initialize action controller here */
        $locale = Bootstrap::$config['resources']['locale']['default'];
        $path = dirname(__FILE__)."/../languages/$locale/data.ini";
        $ini = new Zend_Config_Ini($path);
        $this->_arrayData = $ini->get('array_data')->toArray();
        $this->view->menuArr = $this->_arrayData['transfer']['menu'];
        $this->view->transfer_kind_arr = $this->_arrayData['transfer']['kind'];
        $this->_teacherSn = Teacher_Model_Teacher::getSnById($this->view->user->username);
    }

    /**
     * 初始化學期
     * @return unknown_type
     */
    private function _initSemester()
    {
        if ($this->getRequest()->getParam('year_semester')){
       	    $this->view->year_semester = $this->getRequest()->getParam('year_semester');
       	    echo $this->getRequest()->getParam('year_semester').'--';
       	    $this->view->class_id = $this->getRequest()->getParam('class_id');
       	}
       	else{
       	    $this->view->year_semester = $this->view->Semester['year'].'-'.$this->view->Semester['semester'];
       	}


    }

    /**
     * 初始化 轉入 Form
     * @return unknown_type
     */
    protected function _initMoveinForm()
    {
        $form = new Student_Form_StudentTransfer();
        $form->addElement('text','student_id',array(
            'label' => 'student_id',
            'required' => true,
        	'size'=>10,
            'filters'    => array('StringTrim','StripTags'),
        )) ->addElement('text','name',array(
            'label' => 'name',
        	'required' => true,
            'size'=>10,
            'filters'    => array('StringTrim','StripTags'),
        )) ->addElement('radio', 'sex', array(
          'label'      => 'sex',
          'required'   => true,
          'filters'    => array('StringTrim'),
          'multiOPtions' => $form->arrayData['sex'],
          'separator'=>'&nbsp;'
          //  'validators' => array()
        ))->addElement('text', 'identification_number', array(
          'label'      => 'identification_number',
          'required'   => true,
          'filters'    => array('StringTrim','StripTags'),
           'size'=>10,
        ))->addElement('text', 'number', array(
          'label'      => 'number',
          'required'   => true,
          'filters'    => array('StringTrim','StripTags'),
           'size'=>10,
        	'validators' => array('int')
        )
        );
        $form->getElement('date')->setValue(date("Y-m-d"));
        $form->removeElement('causation_kind');
        $form->setElementsBelongTo('move_in');

        return $form;
    }


    /**
     * 初始化轉出表單
     * @return Student_Form_StudentTransfer
     */
    protected function _initMoveoutForm()
    {
        //  表單
        $form = new Student_Form_StudentTransfer();
        $arr = array(''=>'choose kind');
        foreach($this->_arrayData['transfer']['out'] as $id=>$val)
        $arr[$id] = $val;

        $form->getElement('causation_kind')->setmultiOPtions($arr);

        $form->setElementsBelongTo('move_out');

        // $this->view->default_certificate_word = $param_arr['default_certificate_word'];
        return $form;
    }

    /**
     * 初始化復學表單
     * @return Student_Form_StudentTransfer
     */
    protected function _initReinForm()
    {
        //  表單
        $form = new Student_Form_StudentTransfer();
        $arr = array(''=>'choose kind');
        foreach($this->_arrayData['transfer']['rein'] as $id=>$val)
        $arr[$id] = $val;
        // 異動類別
        $form->getElement('causation_kind')->setmultiOPtions($arr);
        // 學校名稱縣市可為 null
        $form->getElement('school_name')->setOptions(array('required'=>false));
        $form->getElement('county')->setOptions(array('required'=>false));

        $semester = new Semester_Model_SemesterClass();
        $className = $semester->getGradeClassNames();
        $className = array_merge(array(''=>'choose a class'),$className);
        // 班級
        $form->addElement('select','selClass',array(
            'label'     => 'transfer to class',
            'required' => true,
            'multiOPtions' => $className
        ));
        // 學生
        $form->addElement('text','studentSn',array(
            'label' => 'student name',
            'required' => true,
            'size' => 12
        ));
        // 學號
        $form->addElement('text','student_id',array(
            'label' => 'student ID',
            'required' => true,
            'size' => 10
        ));
        // 座號
        $form->addElement('text','number',array(
            'label' => 'number',
            'required' => true,
            'size' => 6,
            'filters'    => array('StringTrim'),
            'validators' => array('int'),
        ));

        $form->setElementsBelongTo('rein');

        // $this->view->default_certificate_word = $param_arr['default_certificate_word'];
        return $form;
    }

    /**
     * 搜尋已調出學生
     * @return unknown_type
     */
    public function reinSearchAction()
    {
        $this->getHelper('layout')->disableLayout();
        $this->getHelper('ViewRenderer')->setNorender();
        //        $causationKind = (int) $this->getRequest()->getParam('selOutKind');
        $str = $this->getRequest()->getParam('q');
        if (!$str)
        echo '';
        else {
            $f = new Zend_Filter_StripTags();
            $str = $f->filter($str);
            $q = Doctrine_Query::create()

            ->select('DISTINCT a.sn, a.student_sn AS student_sn, a.year, a.semester,  c.student_id AS  student_id, b.name AS name')
            ->from('Student_Model_StudentTransfer a')
            ->leftJoin('a.Student_Model_Student b')
            ->leftJoin('b.Student_Model_StudentSemester c')
            ->whereIn('a.causation_kind',
            array(Student_Model_StudentTransfer::REDEPLOYMENT,
            Student_Model_StudentTransfer::DROPOUTS,
            Student_Model_StudentTransfer::ABROAD,
            ))
            ->Where("b.name LIKE '%$str%'")
            ->andWhere('b.study_condition <> 0')
            ->andWhere("a.sn = (SELECT MAX(sn) FROM Student_Model_StudentTransfer  WHERE student_sn=a.student_sn)")
            ->orderBy('a.year DESC, a.semester DESC');
            $res = $q->fetchArray();
            foreach($res as $row)
            echo $row['name'].'('.$row['student_id'] .')|'.$row['sn']."\n";
        }

    }


    public function indexAction()
    {
    }

    /**
     * "轉入" 設定
     * @return unknown_type
     */
    public function moveinAction()
    {
        $this->getHelper('layout')->disableLayout();
        $this->_initSemester();
        $form = $this->_initMoveinForm();
        // 取系統預設值
        $param = new Default_Model_ModulesParam();
        $param_arr = $param->getParamByResourceId('student');
        $form->setDefault('approve_organ',$param_arr['default_approve_organ']);
        $form->setDefault('approve_word',$param_arr['default_approve_word']);
        $form->setDefault('causation',$param_arr['default_causation_in']);
        $this->view->form = $form;
    }

    /**
     * "轉入" 修改
     * @return unknown_type
     */
    public function moveinEditAction()
    {
        $updateSn = $this->_teacherSn;
        $sdata = $this->getRequest()->getParams();

        $arr = explode("-",$sdata['year_semester']);
        $year = $arr[0];
        $semester = $arr[1];
        $sdata['move_in']['year'] = $year;
        $sdata['move_in']['semester'] = $semester;
        $sdata['move_in']['causation_kind'] = Student_Model_StudentTransfer::MOVE_IN;
        $form = new Student_Form_StudentTransfer();
        $form->removeElement('causation_kind');
        $form->setElementsBelongTo('move_in');
        if($form->isValid($sdata)) {
            $studentTransfer = new Student_Model_StudentTransfer;
            $data = $sdata['move_in'];
            $arr = array();
            $arr['approve_date'] = $data['approve_date'];
            $arr['approve_number'] = $data['approve_number'];
            $arr['approve_organ'] = $data['approve_organ'];
            $arr['approve_word'] = $data['approve_word'];
            $arr['causation'] = $data['causation'];
            $arr['county'] = $data['county'];
            $arr['date'] = $data['date'];
            $arr['school_id'] = $data['school_id'];
            $arr['school_name'] = $data['school_name'];
            $arr['teacher_sn'] = $updateSn;
            $studentTransfer->assignIdentifier($sdata['sn']);
            $studentTransfer->fromArray($arr);
            $studentTransfer->save();
            //$student_transfer->update($arr,array('sn = ?' => $sdata['sn']));
            $this->_helper->json(array('sn'=>$sdata['sn']));
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
    }

    /**
     * 轉出修改
     * @return unknown_type
     */
    public function moveoutEditAction()
    {
        $updateSn = $this->_teacherSn;
        $sdata = $this->getRequest()->getParams();

        $arr = explode("-",$sdata['year_semester']);
        $year = $arr[0];
        $semester = $arr[1];
        $sdata['move_out']['year'] = $year;
        $sdata['move_out']['semester'] = $semester;
        $form = $this->_initMoveoutForm();
        if($form->isValid($sdata)) {
            $studentTransfer = new Student_Model_StudentTransfer;

            $data = $sdata['move_out'];
            $arr = array();
            $arr['approve_date'] = $data['approve_date'];
            $arr['approve_number'] = $data['approve_number'];
            $arr['approve_organ'] = $data['approve_organ'];
            $arr['approve_word'] = $data['approve_word'];
            $arr['causation'] = $data['causation'];
            $arr['county'] = $data['county'];
            $arr['date'] = $data['date'];
            $arr['school_id'] = $data['school_id'];
            $arr['school_name'] = $data['school_name'];
            $arr['teacher_sn'] = $updateSn;
            $studentTransfer->assignIdentifier($sdata['sn']);
            $studentTransfer->fromArray($arr);
            $studentTransfer->save();
            //$student_transfer->update($arr,array('sn = ?' => $sdata['sn']));
            $this->_helper->json(array('sn'=>$sdata['sn']));
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));

    }

    /**
     * 轉出存檔
     * @return unknown_type
     */
    public function moveoutSetAction()
    {
        $updateSn = $this->_teacherSn;
        $data = $this->getRequest()->getParams();
        $arr = explode("-",$data['year_semester']);
        $year = $arr[0];
        $semester = $arr[1];
        $data['move_out']['year'] = $year;
        $data['move_out']['semester'] = $semester;
        $form = $this->_initMoveoutForm();
        $form->getElement('date')->setValue(date("Y-m-d"));
        //$form->setElementsBelongTo('move_out');
        $message = array();
        if ($form->isValid($data)){
            //找到本學期最後一個異動編號
            $q = Doctrine_Query::create()
            ->select("MAX(school_move_num)  AS mm")
            ->from('Student_Model_StudentTransfer')
            ->where('year=?',$year)
            ->andWhere('semester=?',$semester);
            $res = $q->fetchOne();
            //  echo $res->mm; exit;
            $data['move_out']['school_move_num'] = $res->mm + 1;
            //$data['move_out']['sn'] = null;
            $data['move_out']['teacher_sn'] = $updateSn;
            $data['move_out']['student_sn'] = $data['student_sn'];
            $studentTransfer = new Student_Model_StudentTransfer();
            $studentTransfer->fromArray($data['move_out']);
            $studentTransfer->save();

            $student = new Student_Model_Student();
            $student->assignIdentifier($data['student_sn']);
            $student->update_sn = $updateSn;
            $student->study_condition = $data['move_out']['causation_kind'];
            $student->save();

            $this->_helper->json(array('sn'=>$data['sn']));
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));

    }

    /**
     *  儲存轉入學生資料
     * @return unknown_type
     */
    public function moveinSetAction()
    {

        $updateSn = $this->_teacherSn;
        $sdata = $this->getRequest()->getParams();
        $arr = explode("-",$sdata['year_semester']);
        $year = $arr[0];
        $semester = $arr[1];
        $sdata['move_in']['year'] = $year;
        $sdata['move_in']['semester'] = $semester;
        $sdata['move_in']['causation_kind'] = Student_Model_StudentTransfer::MOVE_IN;

        $form = $this->_initMoveinForm();
        $message = array();
        if ($form->isValid($sdata)){
            $data = $sdata['move_in'];
            $arr = array();
            //$arr['sn'] = null;
            $arr['sex'] = $data['sex'];
            $arr['identification_number'] = $data['identification_number'];
            $arr['name'] = $data['name'];
            $arr['update_sn']  = $updateSn;
            $student = new Student_Model_Student();
            $student->fromArray($arr);
            $student->save();
            $sn = $student->sn ;

            unset($arr);

            // $arr['sn'] = null;
            $arr['year'] = $year;
            $arr['semester'] = $semester;
            $arr['student_sn'] = $sn;
            $arr['student_id'] = $data['student_id'];
            $arr['update_sn']  = $updateSn;
            $arr['grade'] = substr($data['class_id'],0,-2);
            $arr['class_sn'] = substr($data['class_id'],-2);
            $arr['number'] = $data['number'];
            $studentSemester  = new Student_Model_StudentSemester();
            $studentSemester->fromArray($arr);
            $studentSemester->save();

            $data['student_sn'] = $sn;
            //  $data['sn'] = null;

            $data['teacher_sn'] = $updateSn;
            $studentTransfer = new Student_Model_StudentTransfer();
            $studentTransfer->fromArray($data);
            $studentTransfer->save();
            $this->_helper->json(array('sn'=>$data['sn']));
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));

    }

    /**
     * 取出學生異動資料
     * @return unknown_type
     */
    public function getTransferDataByidAction()
    {
        $sn = (int)$this->getRequest()->getParam('sn');

        $q = Doctrine_Query::create()
        ->select('a.name, a.identification_number, a.sex,
          b.student_id AS student_id, b.grade AS grade, b.class_sn AS class_sn, b.number AS number,
          c.sn AS sn, c.year AS year, c.semester AS semester, c.causation_kind AS causation_kind, c.county AS county,
          c.school_name AS school_name, c.school_id AS school_id, c.date AS date, c.approve_organ AS approve_organ
          , c.causation AS causation, c.approve_date AS approve_date, c.approve_word AS approve_word,
          c.approve_number AS approve_number, c.teacher_sn AS teacher_sn,c.update_time AS update_time,c.student_sn AS student_sn
          ')
        ->from('Student_Model_Student a')
        ->leftJoin('a.Student_Model_StudentSemester  b')
        ->leftJoin('a.Student_Model_StudentTransfer c')
        ->where('b.year=c.year AND b.semester=c.semester')
        ->andWhere('c.sn=?',$sn)
        ;

        $res = $q->fetchOne(null,Doctrine_Core::HYDRATE_ARRAY);

        $this->_helper->json($res);
    }

    /**
     * 取得最後一筆學號
     * @return unknown_type
     */
    public function getlastidAction()
    {
        $request = $this->getRequest();
        $arr = explode("-",$request->getParam('year_semester'));
        $year = (int)$arr[0];
        $semester =(int) $arr[1];
        $arr = explode("-", $request->getParam('grade'));
        $grade = (int) $arr[0];
        $class_sn = (int) $arr[1];
        $result = array();

        $q = Doctrine_Query::create()
        ->select('MAX(student_id) AS mm')
        ->from('Student_Model_StudentSemester')
        ->where('year=?',$year)
        ->andWhere('semester=?',$semester)
        ->andWhere('grade=?',$grade);
        $res = $q->fetchOne(null,Doctrine_Core::HYDRATE_ARRAY);
        $result['student_id'] = $res['mm']+1;

        $q = Doctrine_Query::create()
        ->select('MAX(number) AS mm')
        ->from('Student_Model_StudentSemester')
        ->where('year=?',$year)
        ->andWhere('semester=?',$semester)
        ->andWhere('grade=?',$grade)
        ->andWhere('class_sn=?',$class_sn);
//        echo $q->getSqlQuery();
//        echo $year.' '.$semester.' '.$grade.' '.$class_sn;
        $res = $q->fetchOne(null,Doctrine_Core::HYDRATE_ARRAY);
        $result['number'] = $res['mm']+1;

        $this->_helper->json($result);
    }

    public function getclassAction()
    {
        $this->getHelper('layout')->disableLayout();
        $year_semester = $this->getRequest()->getParam('year_semester');

        echo $this->view->ClassNameOptions($year_semester);
        exit;
    }

    public function queryidAction()
    {
        $request = $this->getRequest();
        $arr = explode("-",$request->getParam('year_semester'));
        $year = (int)$arr[0];
        $semester =(int) $arr[1];
        $student_id = $request->getParam('student_id');
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
        ->from(array('a'=>'student_semester'),array())
        ->join(array('b'=>'student'),'a.student_sn=b.sn',array('name'))
        ->where('a.year=?',$year)
        ->where('a.semester=?',$semester)
        ->where('a.student_id=?',$student_id) ;
        $res = $db->fetchOne($select);
        if ($res)
        $this->_helper->json($res);
        else
        $this->_helper->json(0);
    }


    /**
     * 取出 學期轉入學生資料
     * @return unknown_type
     */
    public function getStudentAction()
    {
        $this->getHelper('layout')->disableLayout();
        $request = $this->getRequest();
        if ($request->getParam('year_semester')) {
            $arr = explode("-",$request->getParam('year_semester'));
            $year = (int)$arr[0];
            $semester =(int) $arr[1];
        }
        else {
            $year = $this->view->Semester['year'];
            $semester = $this->view->Semester['semester'];
        }
        $kind = $request->getParam('kind');
        $q = Doctrine_Query::create()
        ->select('a.*, b.name AS name, b.identification_number AS dentification_number, b.sex AS sex,
          c.student_id AS student_id, c.grade AS grade, c.class_sn AS class_sn, c.number AS number')
        ->from('Student_Model_StudentTransfer a ')
        ->leftJoin('a.Student_Model_Student  b')
        ->leftJoin('b.Student_Model_StudentSemester c')
        ->where('a.year=c.year AND a.semester=c.semester')
        ->andWhere('c.year=?', $year)
        ->andWhere('c.semester=?',$semester);
        switch ($kind) {
            case 'out':
                $q->andWhereIn('a.causation_kind',array(
                Student_Model_StudentTransfer::ABROAD,
                Student_Model_StudentTransfer::REDEPLOYMENT,
                Student_Model_StudentTransfer::REDEPLOYMENT,
                ));
                $tpl = 'moveout/getoutstudent';
                break;
            case 'in' :
                $q->andWhere('a.causation_kind=?',Student_Model_StudentTransfer::MOVE_IN);
                $tpl = 'movein/getinstudent';
                break;

            case 'rein':
                $q->andWhereIn('a.causation_kind',array(
                Student_Model_StudentTransfer::DROPOUTS_RETURN,
                Student_Model_StudentTransfer::LEAVE_RETURN,
                Student_Model_StudentTransfer::TRANSFER_RETURN
                ));
                $tpl = 'rein/getreinstudent';
                break;

            case 'demove':
                $q->andWhereIn('a.causation_kind',array(
                Student_Model_StudentTransfer::UPGRADE,
                Student_Model_StudentTransfer::DOWNGRADE
                ));
                $tpl = 'demove/getstudent';
                break;
        }

        $q->orderBy('a.date DESC');

        $res = $q->fetchArray();
        //print_r($res);
        $this->view->data = $res;
        $this->render($tpl);
    }


    /**
     * 轉出學生設定
     * @return unknown_type
     */
    public function moveoutAction()
    {
        $this->getHelper('layout')->disableLayout();
        $this->_initSemester();
        $form = $this->_initMoveoutForm();
        //取系統預設值
        $param = new Default_Model_ModulesParam();
        $param_arr = $param->getParamByResourceId('student');

        $form->setDefault('approve_date',date("Y-m-d"));
        $form->setDefault('approve_organ',$param_arr['default_approve_organ']);
        $form->setDefault('approve_word',$param_arr['default_approve_word']);
        $form->setDefault('causation',$param_arr['default_causation_out']);
        $form->setDefault('date',date("Y-m-d"));
        $this->view->form = $form;
    }

    /**
     * 班級學生名單
     * @return unknown_type
     */
    public function getstudentsAction()
    {
        $this->getHelper('layout')->disableLayout();
        $yearSemester = $this->getRequest()->getParam('year_semester');
        if (!$yearSemester)
        $yearSemester = $this->view->Semester['year'].'-'.$this->view->Semester['semester'];
        $class_id = $this->getRequest()->getParam('class_id');
        echo $this->view->StudentNameOptions($yearSemester,$class_id);
        exit;
    }

    /**
     * 復學
     * @return unknown_type
     */
    public function reinAction()
    {
        $this->getHelper('layout')->disableLayout();

        $form = $this->_initReinForm();
        //取系統預設值
        $param = new Default_Model_ModulesParam();
        $param_arr = $param->getParamByResourceId('student');

        $form->setDefault('approve_organ',$param_arr['default_approve_organ']);
        $form->setDefault('approve_word',$param_arr['default_approve_word']);
        $form->setDefault('causation',$param_arr['default_causation_out']);

        $this->view->form = $form;
    }

    /**
     * 設定復學學生資料
     * @return unknown_type
     */
    public function reinSetAction()
    {
        $data  = $this->getRequest()->getParams();
        $data['rein']['year'] = $this->view->Semester['year'];
        $data['rein']['semester'] = $this->view->Semester['semester'];
        //        print_r($data); exit;
        $form = $this->_initReinForm();
        if ($form->isValid($data)){
            $row = $data['rein'];

            // 取出 student_sn
            $q = Doctrine_Query::create()
            ->select('student_sn')
            ->from('Student_Model_StudentTransfer')
            ->where('sn=?', $row['sn']);
            $trRow = $q->fetchOne(null,Doctrine_Core::HYDRATE_ARRAY);
            //print_r($trRow); exit;

            // 更改就學狀態
            $q = Doctrine_Query::create()
            ->update('Student_Model_Student')
            ->set('study_condition','?',Student_Model_StudentTransfer::IN_STADY)
            ->where('sn=?', $trRow['student_sn']);
            $q->execute();
            // 加入學期設定
            $q = Doctrine_Query::create()
            ->select('sn')
            ->from('Student_Model_StudentSemester')
            ->where('year=?', $row['year'])
            ->andWhere('semester=?', $row['semester'])
            ->andWhere('student_id=?', $row['student_id']);
            $res = $q->fetchOne();

            $studentSemester = new Student_Model_StudentSemester();
            if ($res) {
                $studentSemester->assignIdentifier($res->sn);
            }
            $studentSemester->year = $row['year'];
            $studentSemester->semester = $row['semester'];
            $studentSemester->student_sn = $trRow['student_sn'];
            $studentSemester->student_id = $row['student_id'];
            $studentSemester->grade = (int)substr($row['selClass'],0,1);
            $studentSemester->class_sn = (int)substr($row['selClass'],1);
            $studentSemester->number = $row['number'];
            $studentSemester->save();


            // teacher_sn
            $teacherSn = $this->_teacherSn;

            // 加入復學記錄
            $studentTransfer = new Student_Model_StudentTransfer();
            $studentTransfer->year = $row['year'];
            $studentTransfer->semester = $row['semester'];
            $studentTransfer->causation_kind = $row['causation_kind'];
            $studentTransfer->student_sn = $trRow['student_sn'];
            $studentTransfer->date = $row['date'];
            $studentTransfer->approve_organ = $row['approve_organ'];
            $studentTransfer->approve_date = $row['approve_date'];
            $studentTransfer->approve_word = $row['approve_word'];
            $studentTransfer->approve_number = $row['approve_number'];
            $studentTransfer->teacher_sn = $teacherSn;
            $studentTransfer->save();

            $this->_helper->json(1);
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
    }

    /**
     * 復學資料修改
     * @return unknown_type
     */
    public function reinEditAction()
    {
        $data  = $this->getRequest()->getParams();
        $data['rein']['year'] = $this->view->Semester['year'];
        $data['rein']['semester'] = $this->view->Semester['semester'];
        $form = $this->_initReinForm();
        if ($form->isValid($data)) {
            $dataRein = $data['rein'];
            $teacherSn = $this->_teacherSn;
            $dataRein['teacher_sn'] = $teacherSn;
            $studentTransfer = new Student_Model_StudentTransfer();
            $studentTransfer->assignIdentifier($dataRein['sn']);
            unset($dataRein['student_sn']);
            $studentTransfer->fromArray($dataRein);
            $studentTransfer->save();

            $this->_helper->json(1);
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
    }

    /**
     * 初始升級表單
     * @return unknown_type
     */
    protected function _initDemoveForm()
    {
        $form = new Student_Form_StudentTransfer();

        $form->getElement('causation_kind')->setOptions(array('required'=>false));
        // 學校名稱縣市可為 null
        $form->getElement('school_name')->setOptions(array('required'=>false));
        $form->getElement('county')->setOptions(array('required'=>false));

        $semester = new Semester_Model_SemesterClass();
        $className = $semester->getGradeClassNames();
        $className = array_merge(array(''=>'choose a class'),$className);
        // 班級
        $form->addElement('select','selClass',array(
            'label'     => 'transfer to class',
            'required' => true,
            'multiOPtions' => $className
        ));
        // 學生
        $studentArr = array(''=>'choose a student');
        $form->addElement('select','studentSn',array(
            'label'     => 'student name',
            'required' => true,
            'multiOPtions' => $studentArr,
        ));
        // 升降班級
        $form->addElement('select','toClass',array(
            'label'     => 'choose to class',
            'required' => true,
            'multiOPtions' => array(''=>'choose a class')
        ));
        $form->setElementsBelongTo('demove');

        $form->getElement('studentSn')->setRegisterInArrayValidator(false);
        $form->getElement('toClass')->setRegisterInArrayValidator(false);
        return $form;
    }
    /**
     * 學生升降級
     * @return unknown_type
     */
    public function demoveAction()
    {
        $this->getHelper('layout')->disableLayout();
        $form = $this->_initDemoveForm();
        $form->getElement('date')->setValue(date("Y-m-d"));
        $form->getElement('approve_date')->setValue(date("Y-m-d"));
        //取系統預設值
        $param = new Default_Model_ModulesParam();
        $param_arr = $param->getParamByResourceId('student');

        $form->setDefault('approve_organ',$param_arr['default_approve_organ']);
        $form->setDefault('approve_word',$param_arr['default_approve_word']);
        $form->setDefault('causation',$param_arr['default_causation_out']);
        $this->view->form = $form;

    }

    public function demoveSetAction()
    {
        $form = $this->_initDemoveForm();

        $data = $this->getRequest()->getParams();
        $data['demove']['year'] = $this->view->Semester['year'];
        $data['demove']['semester'] = $this->view->Semester['semester'];


        if ($form->isValid($data)) {
            $row = $data['demove'];
            // 判斷升降級
            if ((int)$row['selClass'] > (int)$row['toClass'])
            $row['causation_kind'] = Student_Model_StudentTransfer::DOWNGRADE;
            else
            $row['causation_kind'] =  Student_Model_StudentTransfer::UPGRADE;
            // 更改學生學期班級記錄  Student_Model_StudentSemester
            // 找到 本學期記錄
            $q = Doctrine_Query::create()
            ->select('sn')
            ->from('Student_Model_StudentSemester')
            ->where('year=?', $row['year'])
            ->andwhere('semester=?', $row['semester'])
            ->andWhere('student_sn=?', $row['studentSn']);
            $res  = $q->fetchOne();
            if ($res) {
                $grade = (int)substr($row['toClass'], 0, 1);
                $classSn = (int)substr($row['toClass'], 1);
                $q = Doctrine_Query::create()
                ->update('Student_Model_StudentSemester')
                ->set('grade', '?', $grade)
                ->set('class_sn', '?', $classSn)
                ->where('sn=?', $res->sn);
                $q->execute();
            }
            // 新增異動資料 Student_Model_StudentTransfer
            $row['student_sn'] = $row['studentSn'];
            $row['teacher_sn'] = $this->_teacherSn;
            $studentTransfer = new Student_Model_StudentTransfer();
            $studentTransfer->fromArray($row);
            $studentTransfer->save();
            // TODO 考慮入學年是否更改
            $this->_helper->json(1);
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
    }

    /**
     * 升降級學生修改
     * @return unknown_type
     */
    public function demoveEditAction()
    {
        $form = $this->_initDemoveForm();
        $form->getElement('selClass')->setOptions(array('required'=>false));
        $form->getElement('studentSn')->setOptions(array('required'=>false));
        $form->getElement('toClass')->setOptions(array('required'=>false));
        $data = $this->getRequest()->getParams();
        $data['demove']['year'] = $this->view->Semester['year'];
        $data['demove']['semester'] = $this->view->Semester['semester'];

        if ($form->isValid($data)) {
            //取出修改 sn
            $sn = $data['sn'] ;
            $row = $data['demove'];
            $q = Doctrine_Query::create()
            ->update('Student_Model_StudentTransfer')
            ->set('date','?',$row['date'])
            ->set('approve_organ','?', $row['approve_organ'])
            ->set('approve_date','?', $row['approve_date'])
            ->set('approve_word','?', $row['approve_word'])
            ->set('approve_number','?', $row['approve_number'])
            ->where('sn=?', $sn);
            // update student_transfer
            $q->execute();

            $this->_helper->json(1);
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
    }

    /**
     * 初始化新生入學表單
     * @return unknown_type
     */
    protected function _initMovenewForm()
    {
        $form = new Student_Form_StudentTransfer();

        $form->getElement('causation_kind')->setOptions(array('required'=>false));
        // 學校名稱縣市可為 null
        $form->getElement('school_name')->setOptions(array('required'=>false));
        $form->getElement('county')->setOptions(array('required'=>false));
        // label 更改為生效日期
        $form->getELement('date')->setOPtions(array('label'=>'commencement'));
        $semester = new Semester_Model_SemesterDuration();
        $yearArr = $semester->getAllYear();

        // 學年選單
        $tempArr[''] = 'select ..';
        foreach($yearArr as $id=>$val)
        $tempArr[$id] = $val;


        $form->addElement('select','selYear',array(
            'label'     => 'choose year',
            'required' => true,
            'multiOptions' => $tempArr,
        ));
        $form->setElementsBelongTo('movenew');
        return $form;
    }

    /**
     * 新生編班
     * @return unknown_type
     */
    public function movenewAction()
    {
        $this->getHelper('layout')->disableLayout();
        $form = $this->_initMovenewForm();
        $this->view->form = $form;
    }

    /**
     * 設定新生編班
     * @return unknown_type
     */
    public function movenewSetAction()
    {
        $form = $this->_initMovenewForm();
        $form->getElement('year')->setOptions(array('required'=>false));
        $form->getElement('semester')->setOptions(array('required'=>false));
        $res = $this->getRequest()->getParams();
        if ($form->isValid($res)) {
            $row = $res['movenew'];
            $year = $row['selYear'];
            $semester =  1 ; // 預設記錄在第一學期
            $teacherSn = Teacher_Model_Teacher::getSnById();
            // 找出非轉入生資料
            $q = Doctrine_Query::create()
            ->select('a.sn')
            ->from('Student_Model_Student a')
            ->where('a.enroll_year=?', $year)
            ->andwhere('a.sn  NOT IN  (SELECT b.student_sn FROM  Student_Model_StudentTransfer b WHERE  b.causation_kind = ?)',
            Student_Model_StudentTransfer::MOVE_IN);
            $res = $q->fetchArray();
            foreach($res as $val) {
                $studentTransfer = new Student_Model_StudentTransfer();
                $st = $studentTransfer->getTable()->findBySql("year=? AND student_sn=?",array($year,$val['sn']))->getFirst();
                // 修改
                if ($st){
                    $st->date = $row['date'];
                    $st->approve_organ = $row['approve_organ'] ;
                    $st->approve_date  = $row['approve_date'];
                    $st->approve_word = $row['approve_word'];
                    $st->approve_number = $row['approve_number'];
                    $st->teacher_sn = $teacherSn;
                    $st->save();
                }
                else {
                    $studentTransfer->year = $year;
                    $studentTransfer->semester = $semester;
                    $studentTransfer->date = $row['date'];
                    $studentTransfer->approve_organ = $row['approve_organ'] ;
                    $studentTransfer->approve_date  = $row['approve_date'];
                    $studentTransfer->approve_word = $row['approve_word'];
                    $studentTransfer->approve_number = $row['approve_number'];
                    $studentTransfer->teacher_sn = $teacherSn;
                    $studentTransfer->student_sn = $val['sn'];
                    $studentTransfer->causation_kind = Student_Model_StudentTransfer::NEW_STUDENT;
                    $studentTransfer->save();
                }

            }
            $this->_helper->json(1);
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
        // TODO
        // 學年度每一個學生異動記錄
        // 由 student_semester 中查詢學年度學生
        // 或由學生入學年查詢

    }

    /**
     * 新生男女人數統計
     * @return unknown_type
     */
    public function getNewStudentCountAction()
    {
        // 扣除轉入生
        $year = (int)$this->getRequest()->getParam('year');
        $q = Doctrine_Query::create()
        ->select('a.sex,COUNT(*) AS cc')
        ->from('Student_Model_Student a')
        ->where('a.enroll_year=?', $year)
        ->andwhere('a.sn  NOT IN  (SELECT b.student_sn FROM  Student_Model_StudentTransfer b WHERE  b.causation_kind = ?)',
        Student_Model_StudentTransfer::MOVE_IN)
        ->groupBy('a.sex');

        $res = $q->fetchArray();
        $arr = array();
        //   echo $q->getSqlQuery();
        foreach($res as $row) {
            $arr[$row['sex']] = $row['cc'];
            $arr['count'] += $row['cc'];
        }
        $this->_helper->json($arr);
    }

    /**
     * 新生入學以年度列表
     * @return unknown_type
     */
    public function getNewStudentCountListAction()
    {
        $this->getHelper('layout')->disableLayout();
        $q = Doctrine_Query::create()
        ->select('sn, COUNT(*) AS number,  year, date, approve_date, approve_organ,approve_word,approve_number')
        ->from('Student_Model_StudentTransfer')
        ->where('causation_kind=?', Student_Model_StudentTransfer::NEW_STUDENT)
        ->groupby('year')
        ->orderBy('year DESC');
        $res = $q->fetchArray();
        $this->view->data = $res;
        $this->render('movenew/list');
    }

    /**
     * 以 sn 取一筆新生入學資料
     * @return unknown_type
     */
    public function getOneNewStudentData()
    {
        //        $sn = (int)$this->getRequest()->getParam('sn') ;
        //        $q = Doctrine_Query::create()
        //        //>from('Student_Model_Trans')
    }
    public function reportAction()
    {
        $this->getHelper('layout')->disableLayout();
    }

    public function certificateAction()
    {
        $this->getHelper('layout')->disableLayout();
    }

    public function tablesAction()
    {
        $this->getHelper('layout')->disableLayout();
    }

}